Datenmodellierung

VU 181.117 (2.0)

Sommersemster 2005

Beispiel 2 - SQL-SELECTs
Angabe

Abgabeschluss für das SQL-Beispiel ist der 17. Juni.
Um zur Prüfung am 17. Juni antreten zu dürfen, müssen Sie das Beispiel bis spätestens 16. Juni (dem Tag vor der Prüfung) korrekt abgeben.
Geben Sie Ihre Lösungen erst nach dem 16. Juni korrekt ab, so können Sie erst bei einem der Prüfungstermine im Herbst antreten.
Die 5 Bonuspunkte für dieses Beispiel bekommen Sie, falls Sie dieses bis 24. Mai korrekt abgeben.

Die Anzahl der zu lösenden Abfragen richtet sich nach dem Zeitraum, in der die Beispiele abgageben werden:

Abgabewoche	Anzahl der zu lösenden Abfragen
18. - 24. 5.	18
25. 5. - 10. 6.	20
13. - 17. 6.	22

Bei der Abgabe sind bitte die folgenden Dinge mitzubringen/vorzubereiten:

Ihr Studentenausweis
Ein mit Ihren Namen versehener Ausdruck Ihrer SQL-Statements
Der Code Ihrer SQL-Statemenst in elektronischer Form:

auf Diskette oder USB-Stick, oder
als File in Ihrem Homedirektory im InfoLab, oder
irgendwo im WWW (homepage, Mailbox, etc.), wobei Sie und der Tutor problemlos aus dem InfoLab darauf zugreifen können müssen

Bei der Abgabe

werden die Beispiele

auf Vollständigkeit und
Korrektheit überprüft

müssen Sie

Ihre Lösungen

selbstständig erklären können (ohne, dass Ihnen der Tutor "die Würmer aus der Nase ziehen muss")

an geänderte Abfragen anpassen können

neue Abfragen lösen können
die Ergebnisse von SQL-Abfragen auf Testdaten ausrechnen können
theoretsiche Fragen zu SQL beantworten können

Alle diese Punkte sind für jemanden, der die Abfragen selbstständig gelöst hat problemslos zu bewältigen.

Die Datenbank (PostgreSQL 7.2) mit Testdaten finden Sie unter:

http://websql.dbai.tuwien.ac.at/cc_fixed/

weitere Datenbanken zum Testen und Üben finden Sie unter:

http://websql.dbai.tuwien.ac.at/

Die Datenbanken sind durch ein Passwort geschützt (Passwort vergessen?).

Sie können die Beispiele auch auf einer anderen, eigenen Datenbank lösen, als mit der von uns zur Verfügung gestellten (CREATE Statements, Testdaten und DROPs). Ihre Abfragen müssen jedoch auf der Übungsdatenbank laufen und die richtigen Ergebnisse liefern. (Bitte beachten Sie, dass es beim Einspielen der Daten in andere Datenbanken zu Kompatibilitätsproblemen kommen kann, bei deren Behebung wir Ihnen nicht behilflich sein können. Informtionen zu diesen Themen können Sie jedoch in den entsprechenden Foren finden.)

Wir behalten uns vor, die Testdaten im Laufe der Übung zu ändern (Sie können dies allerdings nicht tun). Dies sollte keine Auswirkung auf funktionierende Abfragen haben. Lediglich das Resultat könnte sich dadurch ändern. Richtige Abfragen liefern immer das richtige Ergebnis, unabahängig von den Testdaten - eine Abfrage ist nicht richtig, nur weil sie bei bestimmten Testdaten das richtige Ergebnis liefert.

Filmdatenbank

Relationen

Person (SVNr, Name, GeburtsDatum, SterbeDatum);
Schauspieler (SVNr)
Crew (SVNr)
Figur (Name)
Taetigkeit (Job)
Film (Titel, Jahr, DrehBeginn, DrehEnde, Dauer, Land)
Fortsetzung (Titel, Jahr, Sprache, VonTitel, VonJahr)
FremdTitel (Titel, Jahr, Sprache, OrigTitel, OrigJahr)
Genre (Genre)
hatGenre (Titel, Jahr, Genre)
spielt (SVNr, Titel, Jahr, Name)
spricht (SVNr, Titel, Jahr, Name)
macht (SVNr, Titel, Jahr, Job)

CREATE - Statements (nur zur Info, bereits in der LVA-Datenbank enthalten)

Beschreibung

Personen haben eine eindeutige SVNr, sowie einen Namen und ein Geburtsdatum. Bei verstorbenen Personen ist das Sterbedatum verzeichnet, bei lebenden Personen ist dieser Wert NULL.
Bei Personen können sowohl Schauspieler als auch Mitglieder der Crew ein.
Schauspieler spielen in Filmen bestimmte Figuren. Sie können aber auch (z.B. bei Zeichentrickfilmen) diese Figuren nur sprechen.
Mitglieder der Crew übernehmen bei einem Film bestimmte Tätigkeiten (Regie, Drehbuch, etc.).
Filme werden durch die Kombination aus Titel und Erscheinungsjahr identifiziert. Für sie wird der Beginn und das Ende der Dreharbeiten, die Dauer und das Land in dem sie erschienen sind gespeichert.
Jeder Film ist einem oder mehreren Genres zugeordnet.
Des weiteren können Filme auch in andere Sprachen übersetzt werden. Dabei wird die Fremdsprache, der Titel in dieser Sprache und das Jahr in dem die Synchronisation erschienen ist vermerkt.
Wenn ein Film die Fortsetzung eines anderen Filmes ist, so wird auch diese Tatsache gespeichert, wobei sich "Fortsetzung" in diesem Zusammenhang auf den Inhalt der Filme bezieht, nicht auf den Zeitpunkt des Erscheinens.

Aufgabenstellung

Erstellen Sie nun SQL-Abfragen, welche die folgenden Daten ausgeben:

Geben Sie alle Filme aus, die im Jahr 2003 herausgekommen sind.
Geben Sie alle lebenden Personen aus, sortieren Sie diese nach dem Geburtsdatum.
Geben Sie Name und Geburtsdatum aller Schauspieler aus, die vor dem 11. Mai 1985 geboren wurden aus, sortieren Sie diese nach dem Namen.
Geben Sie alle Schauspieler mit den Filmen in denen sie mitgespielt haben aus.
Geben Sie alle Schauspieler aus, die während Dreharbeiten (an denen sie beteiligt waren) verstorben sind.
Geben Sie alle Figuren aus, die in mindestens 3 Filmen vorkommen und immer vom selben Schauspieler gespielt wurden.
Geben Sie alle Figuren aus, die in mindestens 3 Filmen vorkommen und immer von einem anderen Schauspieler gespielt wurden. Geben Sie des weiteren die Anzhl der entsprechenden Filme, sowie die Anzahl der Schauspieler aus.
Geben Sie alle Personen aus, die in einem Film sowohl Regie geführt, das Drehbuch geschrieben, als auch eine Rolle gespielt haben.
Geben Sie alle Filmpaare aus, bei denen die "Fortsetzung" VOR dem Film selbst gedreht wurde (oder das Prequel nach dem Film).

Geben Sie alle "reinen" Komödien aus (Filme, die kein 2. Genre zugewiesen haben)
Geben Sie alle Schauspieler aus, die schon in einem Spionagefilm, aber noch in keinem Actionfilm mitgespielt haben.

Geben Sie für jeden Schauspieler die folgende Info aus: SVNr, Name, Geburtsdatum, Anzahl der Filme, Anzahl der verschiedenen Rollen, sowie die längste Drehdauer
Gesucht ist der Film mit der kürzesten Drehdauer, wobei die Drehdauer ausgegeben werden soll.
Geben Sie für jeden Film Titel, Jahr, Anzahl der Figuren, Anzahl der beteiligten Schauspieler, sowie die Drehdauer aus.

Geben Sie für alle verstorbenen Regiseure SVNr, Name und die Anzahl der Filme, bei denen Sie Regie geführt haben aus.
Geben Sie alle Filme aus, von denen es keine Fortsetzung gibt.
Geben Sie alle Filme zusammen mit der Anzahl der Fremdsprachen, in die sie übersetzt wurden aus.
Geben Sie den Schauspieler aus, der im Jahr 2003 (Erscheinungsjahr) in den meisten Filmen mitgespielt hat aus.
Geben Sie für jedes Jahr die Anzahl der in diesem Jahr erschienen File aus. Sortieren Sie das Ergebnis absteigend nach der Anzahl der Filme und aufsteigend nach der Jahreszahl.
Geben Sie das Jahr aus, in dem die wenigsten Filme herausgekommen sind.
Geben Sie alle Personen, sowie die Anzahl der Filme an denen sie beteiligt sind aus. Sortieren Sie nach der Anzahl der Filme.
Geben Sie alle Schauspieler aus, die keine anderen Tätigkeiten (also auch nicht als Sprecher) ausgeübt haben.
Geben Sie alle Filme aus, in denen keine Schauspieler mitgespielt haben.
Geben Sie die Sprache aus, bei der es im Durchschnitt am längsten dauert, bis Filme in diese übersetzt werden.
Geben Sie alle Schauspieler aus, zusammen mit der Anzahl der Figuren, die sie gesprochen haben, die Anzahl der Filme, an denen Sie als Sprecher beteiligt waren, sowie welchem Prozentsatz der Filme mit Sprechern dies entspricht.